package com.bodekeji.dati.repository;

import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class DatiRepository {

	@Resource
	private JdbcTemplate jdbcTemplate;
	
	/**
	 * 查询n条随机题目
	 * @param n 题目条数
	 * @return 题目集合
	 */
	public List<Map<String, Object>> findSj(final int n) {
		//定义SQL语句
		String sql = "SELECT * FROM tb_timu ORDER BY RAND() LIMIT ?";
		return jdbcTemplate.queryForList(sql, new Object[] {n});
	}
	
	/**
	 * 插入答题历史
	 * @param openid 微信openid
	 * @param shijian 时间
	 * @param defen 得分
	 * @return
	 */
	public int insertDatilishi(String openid,int defen) {
		String sql = "insert into tb_datilishi(openid,shijian,defen) values(?,now(),?)";
		Object[] args = new Object[] {openid,defen};
		return jdbcTemplate.update(sql,args);
	}
	
	/**
	 * 查询某个openid的答题历史
	 * @param openid 微信openid
	 * @return 答题历史集合
	 */
	public List<Map<String, Object>> findDatilishiByOpenid(String openid){
		String sql = "SELECT id,openid,DATE_FORMAT(shijian, '%Y-%c-%d %h:%i:%s' ) as shijian,defen FROM tb_datilishi where openid=? ORDER BY shijian DESC";
		return jdbcTemplate.queryForList(sql, new Object[] {openid});
	}
	
}
